﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyKho.Common;
using System.Data.SqlClient;

namespace QuanLyKho.DataAccess
{
    public class clsDataAccessUser : iAction
    {
        private SqlConnection conn = new clsConnect().GetSqlConnection();
        private SqlCommand cmd = null;
        private SqlDataReader dr = null;
        private StringBuilder sBuilder = null;

        #region iAction Members

        public int AddNew(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsUser obj = (clsUser)objParam;
                sBuilder = new StringBuilder("UserAdd");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", obj.UserName);
                cmd.Parameters.AddWithValue("@Password", obj.Password);
                cmd.Parameters.AddWithValue("@Fullname", obj.Fullname);
                cmd.Parameters.AddWithValue("@Admin", obj.Admin);
                cmd.Parameters.AddWithValue("@Update", obj.Update);
                cmd.Parameters.AddWithValue("@Read", obj.Read);
                cmd.Parameters.AddWithValue("@Des", obj.Des);
                cmd.Parameters.AddWithValue("@Created", obj.Created);
                cmd.Parameters.AddWithValue("@Position", obj.Position);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public int Update(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsUser obj = (clsUser)objParam;
                sBuilder = new StringBuilder("UserUpdate");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", obj.ID);
                cmd.Parameters.AddWithValue("@UserName", obj.UserName);
                cmd.Parameters.AddWithValue("@Password", obj.Password);
                cmd.Parameters.AddWithValue("@Fullname", obj.Fullname);
                cmd.Parameters.AddWithValue("@Admin", obj.Admin);
                cmd.Parameters.AddWithValue("@Update", obj.Update);
                cmd.Parameters.AddWithValue("@Read", obj.Read);
                cmd.Parameters.AddWithValue("@Des", obj.Des);
                cmd.Parameters.AddWithValue("@Position", obj.Position);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public int Delete(long id)
        {
            try
            {
                int status = 0;
                conn.Open();
                sBuilder = new StringBuilder("UserDelete");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        #endregion

        /// <summary>
        /// Lay tat ca danh sach 
        /// </summary>
        /// <returns>List::</returns>
        public List<clsUser> UserSelectAll()
        {
            try
            {
                List<clsUser> list = new List<clsUser>();
                sBuilder = new StringBuilder("UserSelectAll");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsUser obj = new clsUser();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["UserName"] != DBNull.Value)
                            obj.UserName = Convert.ToString(dr["UserName"]);
                        if (dr["Password"] != DBNull.Value)
                            obj.Password = Convert.ToString(dr["Password"]);
                        if (dr["Fullname"] != DBNull.Value)
                            obj.Fullname = Convert.ToString(dr["Fullname"]);
                        if (dr["Admin"] != DBNull.Value)
                            obj.Admin = Convert.ToBoolean(dr["Admin"]);
                        if (dr["Update"] != DBNull.Value)
                            obj.Update = Convert.ToBoolean(dr["Update"]);
                        if (dr["Read"] != DBNull.Value)
                            obj.Read = Convert.ToBoolean(dr["Read"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Created"] != DBNull.Value)
                            obj.Created = Convert.ToDateTime(dr["Created"]);
                        if (dr["Position"] != DBNull.Value)
                            obj.Position = Convert.ToString(dr["Position"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally 
            { conn.Close(); }
        }

        /// <summary>
        /// Lay tat ca danh sach dua vao ten tai khoan
        /// </summary>
        /// <param name="AccName">string:: Ten tai khoan</param>
        /// <returns>List::</returns>
        public List<clsUser> UserSelectAllWithName(string UserName)
        {
            try
            {
                List<clsUser> list = new List<clsUser>();
                sBuilder = new StringBuilder("UserSelectAllWithName");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", UserName);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsUser obj = new clsUser();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["UserName"] != DBNull.Value)
                            obj.UserName = Convert.ToString(dr["UserName"]);
                        if (dr["Password"] != DBNull.Value)
                            obj.Password = Convert.ToString(dr["Password"]);
                        if (dr["Fullname"] != DBNull.Value)
                            obj.Fullname = Convert.ToString(dr["Fullname"]);
                        if (dr["Admin"] != DBNull.Value)
                            obj.Admin = Convert.ToBoolean(dr["Admin"]);
                        if (dr["Update"] != DBNull.Value)
                            obj.Update = Convert.ToBoolean(dr["Update"]);
                        if (dr["Read"] != DBNull.Value)
                            obj.Read = Convert.ToBoolean(dr["Read"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Created"] != DBNull.Value)
                            obj.Created = Convert.ToDateTime(dr["Created"]);
                        if (dr["Position"] != DBNull.Value)
                            obj.Position = Convert.ToString(dr["Position"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        /// <summary>
        /// Lay mot danh sach dua vao id
        /// </summary>
        /// <param name="id">int::</param>
        /// <returns>List::</returns>
        public List<clsUser> UserSelectOneWithId(long id)
        {
            try
            {
                List<clsUser> list = new List<clsUser>();
                sBuilder = new StringBuilder("UserSelectOneWithId");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsUser obj = new clsUser();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["UserName"] != DBNull.Value)
                            obj.UserName = Convert.ToString(dr["UserName"]);
                        if (dr["Password"] != DBNull.Value)
                            obj.Password = Convert.ToString(dr["Password"]);
                        if (dr["Fullname"] != DBNull.Value)
                            obj.Fullname = Convert.ToString(dr["Fullname"]);
                        if (dr["Admin"] != DBNull.Value)
                            obj.Admin = Convert.ToBoolean(dr["Admin"]);
                        if (dr["Update"] != DBNull.Value)
                            obj.Update = Convert.ToBoolean(dr["Update"]);
                        if (dr["Read"] != DBNull.Value)
                            obj.Read = Convert.ToBoolean(dr["Read"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Created"] != DBNull.Value)
                            obj.Created = Convert.ToDateTime(dr["Created"]);
                        if (dr["Position"] != DBNull.Value)
                            obj.Position = Convert.ToString(dr["Position"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally 
            { conn.Close(); }
        }

        /// <summary>
        /// Lay mot danh sach dua vao ten tai khoan
        /// </summary>
        /// <param name="AccName">string:: Ten tai khoan</param>
        /// <returns>List::</returns>
        public List<clsUser> UserSelectOneWithName(string UserName)
        {
            try
            {
                List<clsUser> list = new List<clsUser>();
                sBuilder = new StringBuilder("UserSelectOneWithName");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", UserName);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsUser obj = new clsUser();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["UserName"] != DBNull.Value)
                            obj.UserName = Convert.ToString(dr["UserName"]);
                        if (dr["Password"] != DBNull.Value)
                            obj.Password = Convert.ToString(dr["Password"]);
                        if (dr["Fullname"] != DBNull.Value)
                            obj.Fullname = Convert.ToString(dr["Fullname"]);
                        if (dr["Admin"] != DBNull.Value)
                            obj.Admin = Convert.ToBoolean(dr["Admin"]);
                        if (dr["Update"] != DBNull.Value)
                            obj.Update = Convert.ToBoolean(dr["Update"]);
                        if (dr["Read"] != DBNull.Value)
                            obj.Read = Convert.ToBoolean(dr["Read"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Created"] != DBNull.Value)
                            obj.Created = Convert.ToDateTime(dr["Created"]);
                        if (dr["Position"] != DBNull.Value)
                            obj.Position = Convert.ToString(dr["Position"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        /// <summary>
        /// Lay ban ghi moi nhat
        /// </summary>
        /// <returns>int:: AccId</returns>
        public long UserGetMaxId()
        {
            try
            {
                long ID = 0;
                sBuilder = new StringBuilder("UserGetMaxId");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        if (dr["ID"] != DBNull.Value)
                            ID = Convert.ToInt64(dr["ID"]);
                    }
                }
                conn.Close();
                return ID;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally { conn.Close(); }
        }

        /// <summary>
        /// Dang nhap tai khoan.
        /// </summary>
        /// <param name="AccName">string:: ten dang nhap.</param>
        /// <param name="AccPass">string:: mat khau dang nhap.</param>
        /// <returns>int
        /// 0:: Dang nhap ko thanh cong.
        /// 1:: Dang nhap thanh cong.
        /// </returns>
        public int UserLogin(string UserName, string Password)
        {
            try
            {
                int status = 0;
                sBuilder = new StringBuilder("UserLogin");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName",UserName);
                cmd.Parameters.AddWithValue("@Password", Password);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        if (dr["UserName"] != DBNull.Value && dr["Password"] != DBNull.Value)
                        {
                            string _UserName = Convert.ToString(dr["UserName"]);
                            string _Password = Convert.ToString(dr["Password"]);
                            if (_UserName == UserName && _Password == Password)
                                status = 1;
                        }
                    }
                }
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        /// <summary>
        /// Thay doi mat khau tai khoan.
        /// </summary>
        /// <param name="AccId">int:: id tai khoan.</param>
        /// <param name="AccPass">string:: Mat khau moi.</param>
        /// <returns>int::
        /// 0:: Thay doi tai khoan ko thanh cong.
        /// 1:: Thay doi tai khoan thanh cong.
        /// </returns>
        public int UserChangePassword(long ID, string Password)
        {
            try {
                int status = 0;
                sBuilder = new StringBuilder("UserChangePassword");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", ID);
                cmd.Parameters.AddWithValue("@Password", Password);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }
    }
}
